import json
from datetime import datetime

import requests
from urllib.parse import urljoin

import pymysql

BASE_URL = 'http://cmp.msa.gov.cn/cbdj_mgt/dataquery/shipCertQuery.action?act=deletion&&certDeletionQO.org_type=10&certDeletionQO.org_code=140000&certDeletionQO.approval_date_startStr=2024-10-01&certDeletionQO.approval_date_endStr=2024-12-31&page=1&rows=10'

headers = {
    'Accept': 'application/json, text/javascript, */*; q=0.01',
    # Content-Type设置为application/json，接口才能返回json数据
    'Content-Type': 'application/json;charset=utf-8'
}
cookies = {
    # OAMAuthnCookie_cmp.msa.gov.cn:80 参数用于登录认证
    'OAMAuthnCookie_cmp.msa.gov.cn:80': 'gW7Zj6T6pKN9FMUq45eO7TxdsW4b4ZzMZgDzlrj7Zd1th%2BDhAaIkcDCSoWSKmAbU%2BkNzITn5n4R%2BylAgPaLzsxLPjZ68TxDgrZwsF2IzbXnmakWDPZr9hvsjpJhwJAbFGa4sHoSE8GahPfL9UT8AIyAzv2gMFruY7YoVj8UBUMWpw%2FYdaXSMgpAFkfGWuxfUBpEyCz4OY1kSf5NK4xlhPZbSb%2F2ZhFoXEVWWet2PBQgXHvO4OFf2Eomk0Fz7s2LHZUmUhandsfY9IIoCIkd1LZiglrJlJ1UZqJX3%2BKVqzZSIqttRDx1eJMqWUgTBDPiXLcsOISe0UV8I8GX0p0T4ApKAlj069VhEf2MlOWPXJC2lcafWgsW4FsOPMR3nyfBngwJSzIZKHdfTGrJjzNs4kgdGnD5yF758v%2BKtW0gyZMqp288nWDQRI1DgrJ9TKvj5OMtMH0ntN9mziBi4FQEjqkvLcz0zN9GH0EIGSxroGOQ%3D',

    # OAMAuthnHintCookie和JSESSIONID 参数用于声明返回接口数据，不配置的会一直返回HTML
    'OAMAuthnHintCookie': '1',
    'JSESSIONID': '6X8YoZymqurZnnTpPlIA-iH2wrtc7UNKcYd-B55j5Qa4L_maMd70!-1812561480!1739874999462'
}

session = requests.Session()
response_lo = session.post(BASE_URL, headers=headers, cookies=cookies)
strData = json.loads(response_lo.text)


if response_lo.status_code == 200:
    # 数据库连接配置
    db_config = {
        'host': '191.254.8.220',
        'user': 'root',
        'password': 'Szmsa@520',
        'db': 'spiders',
        'charset': 'utf8mb4',
        'cursorclass': pymysql.cursors.DictCursor
    }
    # 表名和字段名
    table_name = 'xt_register_deletion_ship'
    # 连接数据库
    connection = pymysql.connect(**db_config)

    try:
        # 开始事务
        with connection.cursor() as cursor:
            # 循环插入每条记录
            for record in strData:
                # 获取需要插入的字段名称
                fields = ', '.join(record.keys())
                # 拼装需要插入字段对应的%号个数
                values = ", ".join(['%s'] * len(record))
                # value值是json的字段无法直接存入库，要转成string
                json_accept_date = record['accept_date']
                json_approval_date = record['approval_date']
                json_create_time = record['create_time']
                json_del_date = record['del_date']
                json_operate_time = record['operate_time']
                # json对象转成string后重新赋值回原来的字段
                str_accept_date = json.dumps(json_accept_date)
                str_approval_date = json.dumps(json_approval_date)
                str_create_time = json.dumps(json_create_time)
                str_del_date = json.dumps(json_del_date)
                str_operate_time = json.dumps(json_operate_time)
                record['accept_date'] = str_accept_date
                record['approval_date'] = str_approval_date
                record['create_time'] = str_create_time
                record['del_date'] = str_del_date
                record['operate_time'] = str_operate_time
                names = record['accept_date']
                print(names)
                #print(tuple(record.keys()))
                # 构建插入语句
                insert_stmt = (
                    "INSERT INTO `{table}` ({fields}) "
                    "VALUES ({values})"
                ).format(
                    table=table_name,
                    fields=fields,
                    values=values
                )
                # 执行sql语句插入数据，record.values()用于获取字段中的值
                cursor.execute(insert_stmt, tuple(record.values()))
                # print("%%%" + insert_stmt)
                # print(">>>" + str(record.values()))
        # 提交事务
        connection.commit()
    finally:
        # 关闭数据库连接
        connection.close()